package org.shock.ss.sms.commons.excel.impt.impl;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.shock.ss.sms.commons.db.ProxoolDbPool;
import org.shock.ss.sms.commons.excel.impt.BaseExcelImport;

public abstract class BaseExcelImportImpl implements BaseExcelImport {
	/**文件类型判断后得到的描述*/
	public String FileIsExcelDescription="";
	/**封装的excel数据*/
	public List excelList=null;
	/**需要关联外键，查询的语句*/
	public Map relationshipSqlMap=null;
	/**需要关联外键的数据*/
	public Map relationshipMap=null;
	/**数据库连接*/
	public Connection conn=null;
	/**验证的错误数据的错误描述*/
	public List<String> errorDataList=new ArrayList<String>();
	/**导入需要执行的sql语句*/
	public List<String> sqlList=new ArrayList<String>();
	/**是否删除原有数据*/
	public boolean deleteOldData=false;
	/**删除的数据的记录数*/
	public int deleteDataNum=0;
	/**扩展字段*/
	public Object object;//扩展字段
	/**插入的记录数*/
	public int optionDataNum=0; 
	/**验证得到数据有错误信息后是否继续导入数据*/
	public boolean dataErrorOption=false;
	/**设置以导入模板的第几行为参照行*/
	public int rightRowNum=1;
	/**对单元格数据的操作 1时 使用replace去空格 2时使用trim去空格 3时不操作*/
	public int cellSpaceOption=1;
	private Object HSSFDataValidation;
	public void out(Object object) {
		//System.out.println(object);
	}
	public int importExcelToDb(String fileContentType, String filepath,Object object)throws Exception {
		int flag=0;
		this.object=object;
		if(fileIsExcel(fileContentType)){//是否是excel文件
			try {
				this.conn=ProxoolDbPool.getConnection();
				this.conn.setAutoCommit(false);
				
				/**读取excel中的数据*/
				this.excelList=readExcelToList(filepath);//读取excel中的数据
				
				this.relationshipSqlMap=getRelationshipSqlMap();
				/**是否有需要关联的外键信息*/
				
				if(this.relationshipSqlMap!=null){//是否有需要关联的外键信息
					relationshipMap=getRelationshipMaps(this.relationshipSqlMap);//查询外键信息
				}
				
				/**验证数据是否有效*/
				this.errorDataList=checkData(this.excelList);//验证数据是否有效
				//System.out.println("dataErrorOption---"+dataErrorOption);
				if(this.errorDataList==null||this.errorDataList.size()==0||dataErrorOption){//不需要数据验证或数据完全正确或数据验证不通过也可以导入数据
					/**将数据转换成sql语句*/
					 this.sqlList=getSqlList(excelList);
					 /**删除数据*/
					 if(deleteOldData){
						 this.deleteDataNum=delOldDate();//删除数据
					 }
					 /***/
					 optionDataNum=optionDate(sqlList);
					 flag=1;//操作完成
				}else{
					flag=-2;//数据验证不通过
				}
				
				
				
				this.conn.commit();
				this.conn.setAutoCommit(true);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				ProxoolDbPool.closeConnection(this.conn);
			}
			
			
			
			return flag;
		}else{
			return -1;//-1为非法文件
		}
		
	}
	

	public Map<String, String> getRelationshipSqlMap() throws Exception {
		relationshipSqlMap=null;
		return relationshipSqlMap;
	}


	public List<String> checkData(List<List> list) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	public List<String> checkDataError() {
		// TODO Auto-generated method stub
		return this.errorDataList;
	}

	public int delOldDate() throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	public boolean fileIsExcel(String fileContentType) throws Exception {
		errorDataList=new ArrayList<String>();
		
		if("application/vnd.ms-excel".equals(fileContentType)){
			this.FileIsExcelDescription="该文件为excel文件";
			return true;
		}else if("application/octet-stream".equals(fileContentType)){
			this.FileIsExcelDescription="该文件正在被其他软件打开，请关闭打开该文件的软件";
			errorDataList.add(FileIsExcelDescription);
			//System.out.println(errorDataList);
			return false;
		}else {
			this.FileIsExcelDescription="该文件不是excel文件，请上传规范的excel文件";
			errorDataList.add(FileIsExcelDescription);
			//System.out.println(errorDataList);
			return false;
		}
	}

	public String getFileIsExcelDescription() {
		// TODO Auto-generated method stub
		return this.FileIsExcelDescription;
	}

	public Map<String, Map> getRelationshipMaps(Map<String, String> map)
			throws Exception {
	 
		ResultSet rs = null;
		PreparedStatement stmt = null;
		Map finallMap=new HashMap();//返回结果的map
		try {
			for (String key : map.keySet()) {   //遍历sql语句map
				 Map resultmap=new HashMap();// 创建一个存放外键和外键名称的子map      
				 String sql= map.get(key)==null?"":map.get(key).toString(); //得到sql语句
				 stmt = this.conn.prepareStatement(sql);
				 rs = stmt.executeQuery();
				 while (rs.next()) {
						resultmap.put(rs.getString(2),rs.getString(1));//将得到的值放入子map
				 }
				 ProxoolDbPool.closeResultSet(rs);
				 ProxoolDbPool.closeStatement(stmt);
				 finallMap.put(key, resultmap);//将子map和对应的key放入返回结果的map中
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);
		}
		//System.out.println(finallMap);
		return finallMap;
	}

	public List<String> getSqlList(List<List> list) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	

	public int optionDate(List<String> list) throws Exception {
		Statement stmt = null;
		int result = 0;
		try {
			stmt = conn.createStatement();
			for(int i=0;i<list.size();i++){
			String sql=list.get(i)==null?"":list.get(i).toString();
			out(sql);
			stmt.addBatch(sql);
			}
			result=stmt.executeBatch().length;
			out(result);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeStatement(stmt);
		}
		return result;
	}

	public void optionDelOldDate(boolean bool) {
		this.deleteOldData=bool;
	}
	
	public List<List> readExcelToList(String filepath,int row) throws Exception {
		this.rightRowNum=row;
		return readExcelToList(filepath);
	}
	
	public List<List> readExcelToList(String filepath) throws Exception {
		 
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filepath));
		
		HSSFSheet sheet = workbook.getSheetAt(0);
		// 获得数据总行
		int rows = sheet.getLastRowNum();
		 
		ArrayList finalResultList = new ArrayList();//得到的数据结果
		if(rows>0&&rows>=this.rightRowNum){//行数大于0且大于等于标题行
			/** 每行记录数 */
			HSSFRow row = null;
			int lastcell = 0;
		 
			try {
				row = sheet.getRow(this.rightRowNum-1);//找到标题行
				lastcell = row.getLastCellNum();//得到最后标题行的单元格数，以这行单元格的个数为参照。其他行单元格大于该行将不读，小于该行将缺少的单元格设置为空字符串
			} catch (Exception e) {
				e.printStackTrace();
				 
			}
			 
			for (int r = 0; r <=rows; r++) {
				row = sheet.getRow(r);
				if(row!=null){
 
					ArrayList tempList = new ArrayList();//存放一行单元格的数据
					for (int i = 0; i <lastcell; i++) {//初始化
						tempList.add("");
					}
					int cellnum=row.getLastCellNum();
						for (int c = 0; c < cellnum; c++) {
							String strCell = "";
							if (c < lastcell) {
								HSSFCell cell = row.getCell(c);
								if(cell!=null){
								strCell=getCellsValue(cell);
								}
								tempList.set(c, strCell);
							}
						}
						finalResultList.add(tempList);
				}
				
			}
			ArrayList list = new ArrayList();// 
			for (int i = 0; i <lastcell; i++) {//初始化
				list.add("");
			}
			finalResultList.remove(list);//删除没有数据的空列表
		}
		
		return finalResultList;
	}
	public static void main(String[] args) {
	}

	public void setDataErrorOption(boolean bool) {
		dataErrorOption=bool;
	}


	public int getOptionDataNum() {
		return this.optionDataNum;
	}

	public List getErrorDataList() {
		return this.errorDataList;
	}
	public String getCellsValue(HSSFCell cell) throws Exception{
		String strCell = ""; 
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				strCell=changeNumber(cell);
				break;
			case HSSFCell.CELL_TYPE_STRING:
				strCell = changeString(cell);
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				strCell = changeBoolean(cell);
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				strCell = changeFormula(cell);
				break;	
			case HSSFCell.CELL_TYPE_BLANK:
				strCell = changeBlank(cell);
				break;
			case HSSFCell.CELL_TYPE_ERROR:
				strCell = changeError(cell);
				break;
			default:
				strCell =  changeDefault(cell);
				break;
			}
		}
		switch (cellSpaceOption) {
		case 1:
			strCell=strCell.replace(" ", "");
			break;
		case 2:
			strCell.trim();
			break;
		case 3:
			break;	
		default:
			break;
		}
		return strCell;
	}
	
	/**转换空格*/
	public String changeBlank(HSSFCell cell) {
		// TODO Auto-generated method stub
		return "";
	}
	/**转换布尔型*/
	public String changeBoolean(HSSFCell cell) {
		// TODO Auto-generated method stub
		return  String.valueOf(cell.getBooleanCellValue()); 
	}
	/**转换其他类型*/
	public String changeDefault(HSSFCell cell) {
		return cell.toString();
	}
	/**转换错误类型*/
	public String changeError(HSSFCell cell) {
		// TODO Auto-generated method stub
		return "";
	}
	/**转换公式类型*/
	public String changeFormula(HSSFCell cell) {
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		return String.valueOf(cell.getNumericCellValue());
	}
	/**转换数字类型*/	
	
	public String changeNumber(HSSFCell cell) {
		DecimalFormat df= new DecimalFormat("0.00");//指定转换的格式
		String strCell= df.format(cell.getNumericCellValue());//将double类型的值转换为String类型
		return strCell;
	}
	/**转换字符串类型*/
	public String changeString(HSSFCell cell) {
		String strCell = cell.getStringCellValue();
		if (strCell.trim().equals("") || strCell.trim().length() <= 0){
			strCell = "";
		}
		return strCell;
	}
	
	
}
